﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Data;
using System.Collections;
using System.Data.SqlClient;



    public class DatabaseUtility
    {
        #region Member Variables

        private OleDbConnection _databaseConnection = null;
        private string _fileName = string.Empty;
        #endregion

        #region Constructors and Finalizer

        /// <summary>
        /// The DatabaseUtility Constructor is used setup and connect to the database.</summary>
        /// <param name="inputFileName">The inputFileName is used to load a specific database file.</param>
        public DatabaseUtility(string inputFileName)
        {
            _fileName = inputFileName;

            ConnectToDatabase(inputFileName);
        }

        public void RestartDatabase()
        {
            _databaseConnection.Close();

            ConnectToDatabase(_fileName);
        }

        #endregion

        #region Methods

        /// <summary>
        /// The ConnectToDatabase method is used to establish a connection to the specified database.</summary>
        /// <param name="inputFileName">The inputFileName is used to load a specific database file.</param>
        private bool ConnectToDatabase(string inputFileName)
        {
            bool connected = false;

            // Create the connection to the mme database file.
            if (inputFileName.Length == 0)
            {
                return false;
            }

            if (File.Exists(inputFileName))
            {
                string connection_string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + inputFileName;
                // setup the connection to the database
                _databaseConnection = new OleDbConnection(connection_string);
                // attempt to open the database.
                try
                {
                    _databaseConnection.Open();
                }
                catch { return false; }
             
                connected = true;
            }
            else
            {
                return false;
            }

            return connected;
        }

        public DataTable QueryDataBaseForTableInformation(string tableName, string additionalSearch)
        {
            string sSql = "";
            DataTable data_table = null;

            if (additionalSearch.Length != 0)
            {
                sSql = "SELECT * FROM " + tableName + additionalSearch;
            }
            else
            {
                sSql = "SELECT * FROM " + tableName;
            }
            OleDbDataAdapter database_adapter = new OleDbDataAdapter(sSql, _databaseConnection);
            DataSet data_set = new DataSet();

            database_adapter.Fill(data_set);

            data_table = data_set.Tables[0];


            return data_table;
        }

        public OleDbDataAdapter GetAdapterFromDatabase(string tableName)
        {
            string sqlQuery = "";
           
            OleDbDataAdapter database_adapter = null;

            if (_databaseConnection.State == ConnectionState.Open)
            {
                sqlQuery = "SELECT * FROM " + tableName;

                 database_adapter = new OleDbDataAdapter(sqlQuery, _databaseConnection);
            }

            return database_adapter;
        }
        /// <summary>
        /// The QueryDatabaseForTableInformation method is used collect data from specified table.</summary>
        /// <param name="tableName">This is the table the caller wants to retrieve all the data from.</param>
        public DataTable QueryDataBaseForTableInformation(string tableName)
        {
            string sqlQuery = "";
            DataTable dataTable = null;

            if (_databaseConnection.State == ConnectionState.Open)
            {
                sqlQuery = "SELECT * FROM " + tableName;

                OleDbDataAdapter database_adapter = new OleDbDataAdapter(sqlQuery, _databaseConnection);

                DataSet data_set = new DataSet();

                database_adapter.Fill(data_set);

                dataTable = data_set.Tables[0];
            }

            return dataTable;

        }
        /// <summary>
        /// The QueryDatabaseForSpecificInformation method is used collect data from specified table.</summary>
        /// <param name="tableName">This is the table the caller wants to retrieve all the data from.</param>
        /// <param name="columnName">This is the column name in the database to query.</param>
        /// <param name="entryNumber">This is the entry number to lookup in the database.</param>
        public DataTable QueryDataBaseForSpecificInformation(string tableName, string columnName, int entryNumber)
        {
            DataTable dataTable = null;

            if (_databaseConnection.State == ConnectionState.Open)
            {
                string sqlQuery = "";

                sqlQuery = "SELECT * FROM " + tableName + " WHERE (" + columnName + " =" + entryNumber + ")";

                OleDbDataAdapter databaseAdapter = new OleDbDataAdapter(sqlQuery, _databaseConnection);

                DataSet dataSet = new DataSet();

                databaseAdapter.Fill(dataSet);

                dataTable = dataSet.Tables[0];
            }
            return dataTable;

        }

        public string GenerateDynamicSqlCommand(string databaseTable, string[] inputColumns)
        {
            // Generate dynamic sql command.
            int tIndex = 0;
            //The sqlInsertCommand will create a string 
            string sqlInsertCommand = "insert into " + databaseTable.ToUpper().Trim() + " (";
            // Fabricate a SQL Insertion string by iterating through all the columns that will be updated.
            foreach (string columnName in inputColumns)
            {

                if (tIndex < inputColumns.Length - 1)
                {
                    sqlInsertCommand += columnName + ", ";
                }
                else
                {
                    sqlInsertCommand += columnName + ") values (";

                }
                tIndex++;
            }

            tIndex = 0;
            foreach (string columnName in inputColumns)
            {

                if (tIndex < inputColumns.Length - 1)
                {
                    sqlInsertCommand += "@" + columnName + ", ";
                }
                else
                {
                    sqlInsertCommand += "@" + columnName + ")";

                }
                tIndex++;
            }
            return sqlInsertCommand;

        }

        public bool AddToDataBase(ArrayList inputData, string tableName, string[] inputColumns)
        {

            OleDbCommand command = new OleDbCommand();

            command.Connection = _databaseConnection;
            command.CommandText = GenerateDynamicSqlCommand(tableName, inputColumns);
            int tIndex = 0;
            // Cycle through the data and insert it into the database.
            foreach (string columnName in inputColumns)
            {
                command.Parameters.AddWithValue("@" + columnName, inputData[tIndex++]);

            }
            // Commit the data to the database.
            try
            {
                command.ExecuteNonQuery();
            }
            catch { }


            return true;
        }

        public void DeleteFromDatabase(string tableName, string columnName, int idNumber)
        {

            string deleteCommand = "DELETE FROM " + tableName + " WHERE " + columnName + " ='" + idNumber + "'";

            OleDbCommand command;
            command = _databaseConnection.CreateCommand();

            command.CommandText = deleteCommand;


            command.ExecuteNonQuery();


        }
        #endregion

    }
